using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
    public class CommentimageDAL
    {
         private SqlConnection sqlCn = null;
        public CommentimageDAL()
        {
            string stringConnection = ConfigurationSettings.AppSettings["connectionstring"].ToString();
            sqlCn = new SqlConnection(stringConnection);
            sqlCn.Open();
        }
        ~CommentimageDAL()
        {
            //this.sqlCn.Close();
            //this.sqlCn.Dispose();
        }

        public void Insert(tbCommentimage image)
        {
            SqlCommand cm = new SqlCommand("User_Insert", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;

            //cm.Parameters.AddWithValue("@IDCommentImage", image.Idcommentimage);
            cm.Parameters.AddWithValue("@CommentImage", image.Comment_image);
            cm.ExecuteNonQuery();
        }

        public void Update(tbCommentimage image)
        {
            SqlCommand cm = new SqlCommand("User_Update", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@IDCommentImage", image.Idcommentimage);
            cm.Parameters.AddWithValue("@CommentImage", image.Comment_image);
            cm.ExecuteNonQuery();
        }

        public void Delete(int iD)
        {
            SqlCommand cm = new SqlCommand("User_Delete", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;

            cm.Parameters.AddWithValue("@IDCommentImage", iD);

            cm.ExecuteNonQuery();
        }

        public tbCommentimagecollection GET(string Where, int PageIndex, int PageSize, string OrderBy, 
                                       string OrderDirection, out int TotalRecords)
        {
            SqlCommand cm = new SqlCommand("User_GET", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;


            cm.Parameters.AddWithValue("@Where", Where);
            cm.Parameters.AddWithValue("@PageIndex", PageIndex);
            cm.Parameters.AddWithValue("@PageSize", PageSize);
            cm.Parameters.AddWithValue("@OrderBy", OrderBy);
            cm.Parameters.AddWithValue("@OrderDirection", OrderDirection);

            SqlParameter param = new SqlParameter("@TotalRecords", DbType.Int32);
            param.Direction = ParameterDirection.Output;
            cm.Parameters.Add(param);


            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            da.Fill(ds);

            tbCommentimagecollection commentimagecolection =new tbCommentimagecollection();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                tbCommentimage commentimage = new tbCommentimage();
                commentimage.Idcommentimage = int.Parse(ds.Tables[0].Rows[i]["IDCommentImage"].ToString());
                commentimage.Comment_image = ds.Tables[0].Rows[i]["CommentImage"].ToString();
                commentimagecolection.Add(commentimage);
            }
            TotalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());

            return commentimagecolection;
        }
    }
}
